import plotly.graph_objs as go
from plotly.subplots import make_subplots
import pandas as pd
import numpy as np

# 设置时间粒度 - 可选择 'hourly', 'daily' 或 'monthly'
TIME_GRANULARITY = input("设置时间粒度 - 可选择 'hourly', 'daily' 或 'monthly':")  # 修改此处切换时间粒度

# 可配置的错误关键词变量
ERROR_KEYWORD = input("请输入错误原因：")


# 从Excel文件读取数据
def load_data(error_keyword=ERROR_KEYWORD):
    try:
        # 读取Excel文件
        df = pd.read_excel("query_data.xlsx")

        # 数据预处理
        df['hour'] = pd.to_datetime(df['hour'], errors='coerce')

        # 检查并处理可能的空值
        df['ERR_DESCRIPTION'] = df['ERR_DESCRIPTION'].fillna('未知错误').astype(str)

        # 标记指定错误
        df['is_target_error'] = df['ERR_DESCRIPTION'].apply(
            lambda x: 1 if error_keyword in x else 0
        )

        # 添加错误计数列
        df['error_count'] = df['is_target_error'] * df['cmd_count']

        return df

    except Exception as e:
        print(f"数据加载错误: {e}")
        return pd.DataFrame(columns=['hour', 'DEV_LINE', 'ERR_DESCRIPTION', 'cmd_count', 'error_count'])


# 计算按时间粒度的错误数量
def calculate_temporal_errors(df):
    if df.empty or 'hour' not in df.columns:
        return pd.DataFrame(columns=['time_period', 'error_count'])

    df = df.dropna(subset=['hour'])

    # 根据选择的时间粒度进行处理
    if TIME_GRANULARITY == 'hourly':
        # 按小时分组
        temporal = df.groupby(pd.Grouper(key='hour', freq='h'))['error_count'].sum().reset_index()
        temporal = temporal.rename(columns={'hour': 'time_period'})
        temporal['time_label'] = temporal['time_period'].dt.strftime('%H:00')
        chart_title = "按小时指令结果数量"
        xaxis_title = "时间"

    elif TIME_GRANULARITY == 'daily':
        # 按天分组
        temporal = df.groupby(pd.Grouper(key='hour', freq='d'))['error_count'].sum().reset_index()
        temporal = temporal.rename(columns={'hour': 'time_period'})
        temporal['time_label'] = temporal['time_period'].dt.strftime('%Y-%m-%d')
        chart_title = "按天指令结果数量"
        xaxis_title = "日期"

    elif TIME_GRANULARITY == 'monthly':
        # 按月分组
        temporal = df.copy()
        temporal['month'] = temporal['hour'].dt.to_period('M').dt.to_timestamp()
        temporal = temporal.groupby('month')['error_count'].sum().reset_index()
        temporal = temporal.rename(columns={'month': 'time_period'})
        temporal['time_label'] = temporal['time_period'].dt.strftime('%Y年%m月')
        chart_title = "按月指令结果数量"
        xaxis_title = "月份"

    else:
        raise ValueError(f"未知的时间粒度: {TIME_GRANULARITY}. 请选择 'hourly', 'daily' 或 'monthly'")

    return temporal, chart_title, xaxis_title


# 计算按车型的指令结果总数量
def calculate_dev_line_errors(df):
    if df.empty:
        return pd.DataFrame(columns=['DEV_LINE', 'error_count'])

    dev_line = df.groupby('DEV_LINE')['error_count'].sum().reset_index()
    return dev_line.sort_values('error_count', ascending=False)


# 计算指令结果的分布
def calculate_error_distribution(df):
    if df.empty:
        return pd.DataFrame(columns=['指令结果', '命令总数', '百分比'])

    error_df = df[df['is_target_error'] == 1]

    if error_df.empty:
        return pd.DataFrame(columns=['指令结果', '命令总数', '百分比'])

    total_errors = error_df['cmd_count'].sum()
    total_all = df['cmd_count'].sum()

    result = pd.DataFrame({
        '指令结果': [ERROR_KEYWORD],
        '命令总数': [total_errors],
        '百分比': [(total_errors / total_all * 100).round(1)]
    })

    return result


# 主程序
if __name__ == "__main__":
    # 加载数据
    raw_data = load_data()

    # 计算各统计数据
    temporal_errors, temporal_title, xaxis_title = calculate_temporal_errors(raw_data)
    dev_line_errors = calculate_dev_line_errors(raw_data)
    error_dist = calculate_error_distribution(raw_data)

    # 创建子图布局
    fig = make_subplots(
        rows=3, cols=1,
        specs=[[{"type": "bar"}], [{"type": "bar"}], [{"type": "table"}]],
        vertical_spacing=0.1,
        subplot_titles=(
            temporal_title,
            f"按车型配置指令结果数量",
            f"指令结果分布"
        ),
        row_heights=[1, 1, 1]
    )

    # 添加柱状图（按时间粒度错误数量）
    if not temporal_errors.empty:
        fig.add_trace(
            go.Bar(
                x=temporal_errors['time_label'],
                y=temporal_errors['error_count'],
                marker_color='#1f77b4',
                text=temporal_errors['error_count'],
                textposition='outside',
                name=f'{TIME_GRANULARITY}错误数量',
                hovertemplate=(
                    '<b>时间</b>: %{x}<br>'
                    '<b>错误数量</b>: %{y}'
                )
            ),
            row=1, col=1
        )
        fig.update_yaxes(title_text="错误数量", row=1, col=1)

    # 添加柱状图（按车型错误数量）
    if not dev_line_errors.empty:
        fig.add_trace(
            go.Bar(
                x=dev_line_errors['DEV_LINE'],
                y=dev_line_errors['error_count'],
                marker_color='#ff7f0e',
                text=dev_line_errors['error_count'],
                textposition='outside',
                name='车型错误数量',
                hovertemplate=(
                    '<b>车型配置</b>: %{x}<br>'
                    '<b>错误数量</b>: %{y}'
                )
            ),
            row=2, col=1
        )
        fig.update_yaxes(title_text="错误数量", row=2, col=1)

    # 添加错误分布表格
    if not error_dist.empty:
        fig.add_trace(
            go.Table(
                header=dict(
                    values=['指令结果', '命令总数', '百分比(%)'],
                    fill_color='#2a3f5f',
                    align='center',
                    font=dict(size=12, color='white'),
                    height=40
                ),
                cells=dict(
                    values=[
                        error_dist['指令结果'],
                        error_dist['命令总数'],
                        error_dist['百分比'].apply(lambda x: f"{x}%")
                    ],
                    fill_color='#f9f9f9',
                    align='center',
                    font=dict(size=11, color='#2a3f5f'),
                    height=30
                )
            ),
            row=3, col=1
        )
    else:
        fig.add_trace(
            go.Table(
                header=dict(
                    values=['指令结果', '命令总数', '百分比(%)'],
                    fill_color='#2a3f5f',
                    align='center',
                    font=dict(size=12, color='white'),
                    height=40
                ),
                cells=dict(
                    values=[["无数据"], [0], ["0%"]],
                    fill_color='#f9f9f9',
                    align='center',
                    font=dict(size=11, color='#2a3f5f'),
                    height=30
                )
            ),
            row=3, col=1
        )

    # 更新布局
    fig.update_layout(
        height=1500,
        title_text=f"指令失败原因分析报告 ({TIME_GRANULARITY})",
        title_font=dict(size=24, color='#2a3f5f'),
        hovermode="x unified",
        showlegend=False,
        margin=dict(t=150, b=50, l=50, r=50),
        plot_bgcolor='rgba(240, 240, 240, 0.9)'
    )

    # 更新轴标签
    if not temporal_errors.empty:
        fig.update_xaxes(title_text=xaxis_title, row=1, col=1, tickangle=-45)
    if not dev_line_errors.empty:
        fig.update_xaxes(title_text="车型配置", row=2, col=1, tickangle=-45)

    # 保存为HTML文件
    output_filename = f"error_analysis_{TIME_GRANULARITY}.html"
    fig.write_html(output_filename, auto_open=False)
    print(f"指令结果分析报告已保存为 {output_filename}")

